#!/usr/bin/python3
# -*- coding: utf-8 -*-
# ======================================#
# @Author   : duanqizhong
# @time     : 2020/6/8 22:12
# @file     : dboperator.py
# @Software : PyCharm
# ======================================#

from werkzeug.security import generate_password_hash

from apps import log
from apps.models import get_db
from apps.utils import get_current_datetime


def __parse_params_to_insert_sql(table, value_dict):
    """
    :param value_dict:
    :return: fields, vars, args
    """
    if value_dict is None or not isinstance(value_dict, dict):
        return None, None
    fields = []
    args = []
    for key, value in value_dict.items():
        fields.append(f"`{key}`")
        if table == "user" and key == "password":
            args.append(generate_password_hash(value))
        else:
            args.append(str(value))

    other_field = ("create_time", "update_time")
    current_time = get_current_datetime()
    for field in other_field:
        fields.append(f"`{field}`")
        args.append(current_time)

    sql = f"insert into {table} ({','.join(fields)}) values ({','.join(['%s']*len(fields))})"
    return sql, tuple(args)


def __parse_params_to_update_sql(table, value_dict):
    if not isinstance(value_dict, dict):
        return None, None
    update_fields = []
    args = []
    for key, value in value_dict.items():
        if key == "id":
            continue
        update_fields.append(f"{key}=%s")
        if table == "user" and key == "password":
            args.append(generate_password_hash(value))
        else:
            args.append(value)

    update_fields.append("update_time=%s")
    args.append(get_current_datetime())
    args.append(value_dict.get("id"))

    sql = f"update {table} set {','.join(update_fields)} where id=%s"
    return sql, tuple(args)


def insert_table_data(table, value_dict):
    infos = {
        "status": False,
        "msg": "ok"
    }
    db = get_db()
    cursor = db.cursor()
    sql, args = __parse_params_to_insert_sql(table, value_dict)
    if args is None or not len(args):
        infos["msg"] = f"Error: insert data is None!"

        log.error(infos)
        return infos

    try:
        log.info(f"{sql} args:{args}")
        if cursor.execute(sql, args):
            db.commit()
            infos["status"] = True
        else:
            infos["msg"] = f"Exec sql error: {sql}"
            log.error(infos)
    except Exception as e:
        infos["msg"] = f"table: {table}, error: {e}"
        log.error(infos)

    return infos


def update_table_data(table, value_dict):
    infos = {
        "status": False,
        "msg": "ok"
    }
    id_bool = value_dict.get("id", None)
    if id_bool is None:
        infos["msg"] = "Not found id!"
        return infos
    if len(value_dict) < 2:
        infos["msg"] = "Minimum 2 parameters, id is required!"
        return infos

    sql, args = __parse_params_to_update_sql(table, value_dict)

    db = get_db()
    cursor = db.cursor()
    try:

        log.info(f"{sql} args:{args}")

        cursor.execute(sql, args)
        db.commit()
        infos["status"] = True
    except Exception as e:
        infos["msg"] = f"table: {table}, error: {e}"

        log.error(infos)
    return infos


def delete_table_data(table, row_id):
    infos = {
        "status": False,
        "msg": "ok"
    }
    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute(f"delete from {table} where id=%s", (row_id,))
        db.commit()
        infos["status"] = True
    except Exception as e:
        infos["msg"] = f"table: {table}, error: {e}"
        log.error(infos)

    return infos


def update_data_by_where(table, value_dict):
    infos = {
        "status": False,
        "msg": "ok"
    }
    db = get_db()
    cursor = db.cursor()
    sql, args = __parse_params_to_update_sql(table, value_dict)
    try:

        log.info(f"{sql} args:{args}")
        cursor.execute(sql, args)
        db.commit()
        infos["status"] = True
    except Exception as e:
        infos["msg"] = f"table: {table}, error: {e}"

        log.error(infos)

    return infos


def query(sql, data=None, fetchone=False):
    infos = {
        "status": False,
        "data": [],
        "msg": "ok"
    }
    if data is not None and not isinstance(data, list):
        infos["msg"] = f"{data} is not a list"

        log.error(infos)

        return infos

    db = get_db()
    cursor = db.cursor()
    try:

        log.info(f"{sql} args:{data}")

        cursor.execute(sql, data)
        if fetchone:
            infos["data"] = cursor.fetchone()
        else:
            infos["data"] = cursor.fetchall()
        infos["status"] = True
    except Exception as e:
        infos["msg"] = f"Error: {e}"

        log.error(infos)

    return infos


def query_table_data(table, where=None, fetchone=False, is_and=True):
    infos = {
        "status": False,
        "data": [],
        "msg": "ok"
    }
    sql = f"select * from {table}"
    args = []
    if where is not None and len(where):
        if not isinstance(where, dict):
            infos["msg"] = f"{where} is not a dict"
            return infos
        fields = []
        for key, value in where.items():
            fields.append(f"{key}=%s")
            args.append(value)
        if is_and:
            where_str = " and ".join(fields)
        else:
            where_str = " or ".join(fields)
        sql += " where " + where_str

    db = get_db()
    cursor = db.cursor()
    try:
        # 防注入
        log.info(f"{sql} args:{args}")
        cursor.execute(sql, args)
        if fetchone:
            infos["data"] = cursor.fetchone()
        else:
            infos["data"] = cursor.fetchall()
        infos["status"] = True
    except Exception as e:
        infos["msg"] = f"Error: {e}"
        log.error(infos)
    return infos
